﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using ANZ_AIM_DAL;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;

namespace ANZ_AIM_BLL
{
    public class BatchFileSaleInfoUploadBLL
    {
        private string ConnectionString;
        private const string TABLE_NAME = "BatchFileSaleInfoUploads";
        private Dictionary<string, string> ObjectPropDict;

        public BatchFileSaleInfoUploadBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }

        public void Save(BatchFileSaleInfoUpload batchFileSaleInfoChangeUpload, ref int IDReturn)
        {
            try
            {
                
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                string command = string.Empty;
                command = CommandHelper.BuildInsertCommand(batchFileSaleInfoChangeUpload, dict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, ref IDReturn, parameters);
            }
            catch (Exception ex) { throw; }
        }
        public void UpdateStatus(string batchFileSaleInfoID, string status)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                dict.Remove("BatchFileSaleInfoID");
                dict.Remove("FileName");
                dict.Remove("TotalRecord");
                dict.Remove("CreatedDate");
                dict.Remove("Type");
                BatchFileSaleInfoUpload batchFileSaleInfoUpload = new BatchFileSaleInfoUpload() { Status = status, BatchFileSaleInfoID = batchFileSaleInfoID };
                OleDbParameter[] parameters;
                string command = string.Empty;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileSaleInfoID", "BatchFileSaleInfoID"}
                    };
                command = CommandHelper.BuildUpdateCommand(batchFileSaleInfoUpload, dict, conditionDict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
            }
            catch (Exception ex) { throw; }
        }
        public DataTable GetBatchFileByStatus(string status)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                BatchFileSaleInfoUpload batchFileSaleInfoUpload = new BatchFileSaleInfoUpload { Status = status };
                var conditionDict = new Dictionary<string, string>
                    {
                        {"Status", "Status"}
                    };
                string command = CommandHelper.BuildSelectCommand(batchFileSaleInfoUpload, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);
                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public DataTable GetBatchFileByType(string type)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                BatchFileSaleInfoUpload batchFileSaleInfoUpload = new BatchFileSaleInfoUpload { Type = type };
                var conditionDict = new Dictionary<string, string>
                    {
                        {"Type", "Type"}
                    };
                string command = CommandHelper.BuildSelectCommand(batchFileSaleInfoUpload, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);
                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }
        public DataTable Search(string status, string batchFile, string fromDate, string toDate)
        {
            try
            {
                string command = string.Format("SELECT [BatchFileSaleInfoID], [FileName], [TotalRecord], [CreatedDate], [Status], [Type] FROM [{0}] WHERE (@BatchFileSaleInfoID IS NULL OR BatchFileSaleInfoID=@BatchFileSaleInfoID) AND (@Status IS NULL OR Status=@Status) AND CreatedDate BETWEEN @FromDate AND @ToDate;", TABLE_NAME);
                OleDbParameter[] parameters = new OleDbParameter[4];
                if (string.IsNullOrEmpty(batchFile))
                {
                    parameters[0] = new OleDbParameter("@BatchFileSaleInfoID", DBNull.Value);
                }
                else
                {
                    parameters[0] = new OleDbParameter("@BatchFileSaleInfoID", batchFile);
                }
                parameters[1] = new OleDbParameter("@Status", status);
                parameters[2] = new OleDbParameter("@FromDate", fromDate);
                parameters[3] = new OleDbParameter("@ToDate", toDate);

                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public DataTable SearchBatchFileByName(string batchFileName)
        {
            try
            {
                string command = string.Format("SELECT [BatchFileSaleInfoID], [FileName], [TotalRecord], [CreatedDate], [Status], [Type] FROM [{0}] WHERE [FileName]=@batchFileName;", TABLE_NAME);
                OleDbParameter[] parameters = new OleDbParameter[1];
                if (string.IsNullOrEmpty(batchFileName))
                {
                    parameters[0] = new OleDbParameter("@batchFileName", DBNull.Value);
                }
                else
                {
                    parameters[0] = new OleDbParameter("@batchFileName", batchFileName);
                }

                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public void DeleteByBatchFileID(string relatedTableName, string batchFileID){
            try
            {
                string command = string.Format("DELETE * FROM [{0}] WHERE [BatchFileSaleInfoID]=@batchFileID;", TABLE_NAME);
                string command1 = string.Format("DELETE * FROM [{0}] WHERE [BatchFileSaleInfoID]=@batchFileID;", relatedTableName);
                OleDbParameter[] parameters = new OleDbParameter[1];
                OleDbParameter[] parameters1 = new OleDbParameter[1];
                if (string.IsNullOrEmpty(batchFileID))
                {
                    parameters[0] = new OleDbParameter("@batchFileID", DBNull.Value);
                    parameters1[0] = new OleDbParameter("@batchFileID", DBNull.Value);
                }
                else
                {
                    parameters[0] = new OleDbParameter("@batchFileID", batchFileID);
                    parameters1[0] = new OleDbParameter("@batchFileID", batchFileID);
                }
                OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command1, parameters1);
            }
            catch (Exception ex) { throw; }
        }

        public Dictionary<string, string> BuildObjectPropertyMapping()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"BatchFileSaleInfoID", "BatchFileSaleInfoID"},
                {"FileName", "FileName"},
                {"TotalRecord", "TotalRecord"},
                {"CreatedDate", "CreatedDate"},
                {"Type","Type"},
                {"Status", "Status"}
            };

            return dictionary;
        }
    }
}
